package com.fau.wm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.BeanUtils;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.wm.entity.MedicalKeyValue;
import com.fau.wm.entity.MedicalAdmin;
import com.fau.wm.entity.MedicalCallCustomer;
import com.fau.wm.entity.MedicalEnterprise;
import com.fau.wm.entity.MedicalFinancialOrders;
import com.fau.wm.entity.MedicalInStock;
import com.fau.wm.entity.MedicalLogistics;
import com.fau.wm.entity.MedicalOrderReconciliation;
import com.fau.wm.entity.MedicalOrdersAlreadyDelivery;
import com.fau.wm.entity.MedicalOrdersProduct;
import com.fau.wm.entity.MedicalOrdersRetreat;
import com.fau.wm.entity.MedicalOrdersSign;
import com.fau.wm.entity.MedicalOrdersStorage;
import com.fau.wm.entity.MedicalOrdersWaitDelivery;
import com.fau.wm.entity.MedicalRecall;
import com.fau.wm.entity.MedicalRecallDetail;
import com.fau.wm.entity.MedicalStorage;
import com.fau.wm.entity.MedicalStorageDetail;
import com.fau.wm.entity.MedicalWarehouse;
import com.fau.wm.service.IOrdersManagerService;
import com.fau.wm.util.ExcelUtil;
import com.fau.wm.util.Page;
import com.fau.wm.util.ParamUtil;
import com.fau.wm.util.SerialNumUtil;
import com.fau.wm.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class OrdersManagerService extends BaseService implements
		IOrdersManagerService {

	@SuppressWarnings("unchecked")
	@Override
	public Page findOrdersWaitDelivery(MedicalOrdersWaitDelivery ordersWaitDelivery, Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersWaitDelivery a where 1=1 and a.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersWaitDelivery a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(ordersWaitDelivery.getHospitalId());
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getSerialNum())) {
			countSql.append("and a.serialNum=? ");
			statusSql.append("and a.serialNum=? ");
			objectList.add(ordersWaitDelivery.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getExpressId())) {
			countSql.append("and a.expressId=? ");
			statusSql.append("and a.expressId=? ");
			objectList.add(ordersWaitDelivery.getExpressId());
		}
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getName())) {
			countSql.append("and a.name=? ");
			statusSql.append("and a.name=? ");
			objectList.add(ordersWaitDelivery.getName());
		}
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getZipCode())) {
			countSql.append("and a.zipCode=? ");
			statusSql.append("and a.zipCode=? ");
			objectList.add(ordersWaitDelivery.getZipCode());
		}
		
		if(null != ordersWaitDelivery.getStartTime()){
			countSql.append("and a.checkTime>=? ");
			statusSql.append("and a.checkTime>=? ");
			objectList.add(ordersWaitDelivery.getStartTime());
		}
		
		if(null != ordersWaitDelivery.getEndTime()){
			countSql.append("and a.checkTime<=? ");
			statusSql.append("and a.checkTime<=? ");
			objectList.add(ordersWaitDelivery.getEndTime());
		}
		
		if(null != ordersWaitDelivery.getOrderTime()){
			countSql.append("and a.createTime>=? ");
			statusSql.append("and a.createTime>=? ");
			objectList.add(ordersWaitDelivery.getOrderTime());
		}
		
		if(null != ordersWaitDelivery.getOrderTime2()){
			countSql.append("and a.createTime<=? ");
			statusSql.append("and a.createTime<=? ");
			objectList.add(ordersWaitDelivery.getOrderTime2());
		}
		
		//排序
		String[] sortField =(String[]) map.get("sortField");
		String[] sortOrder =(String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersWaitDelivery> list = new ArrayList<MedicalOrdersWaitDelivery>();
		for(Object obj : page.getResult()) {
			ordersWaitDelivery = (MedicalOrdersWaitDelivery) obj;
			// 客服代表
			MedicalAdmin admin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, ordersWaitDelivery.getCusSerId());
			ordersWaitDelivery.setCusSerName(admin.getName());
			// 订单详细
			List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersWaitDelivery.getId());
			String ordersDetail = "";
			for(MedicalOrdersProduct ordersProduct : ordersProductList) {
				String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
				ordersDetail += product + ";";
			}
			ordersWaitDelivery.setOrdersDetail(ordersDetail);
			
			list.add(ordersWaitDelivery);
		}
		page.setResult(list);
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String delivery(MedicalOrdersWaitDelivery ordersWaitDelivery) {
		MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ordersWaitDelivery.getId());
		if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStorage.getOrdersStatus())) {
			return "0";
		} else {
			// 获得订单产品
			@SuppressWarnings("unchecked")
			List<MedicalOrdersProduct> list = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersStorage.getId());
			// 判断库存
			for (MedicalOrdersProduct medicalOrdersProduct : list) {
				MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", ordersWaitDelivery.getWarhouseId(), medicalOrdersProduct.getMedicine().getId());
				if(inStock == null || medicalOrdersProduct.getQuantity() > inStock.getQuantity()){
					return medicalOrdersProduct.getMedicine().getName();
				}
			}
			
			// 全部符合条件后减库存
			for (MedicalOrdersProduct medicalOrdersProduct : list) {
				MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", ordersWaitDelivery.getWarhouseId(), medicalOrdersProduct.getMedicine().getId());
				inStock.setQuantity(inStock.getQuantity() - medicalOrdersProduct.getQuantity());
			}
			
			MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();	
			MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObject(MedicalWarehouse.class, ordersWaitDelivery.getWarhouseId());
			// 生成出库单
			MedicalRecall recall = new MedicalRecall();
			recall.setSerialNum(SerialNumUtil.code());
			recall.setTitle("销售出库");
			recall.setRecallTime(new Date());
			recall.setStatus(SystemConfig.RECALL_STATUS_APPROVAL);
			recall.setWarehouse(warehouse);
			recall.setType(SystemConfig.RECALL_SELL_TYPE);
			recall.setWriteUser(user);
			recall.setHospitalId(user.getEnterpriseId());
			this.iBaseDao.saveObject(recall);
			
			for (MedicalOrdersProduct medicalOrdersProduct : list) {
				MedicalRecallDetail recallDetail = new MedicalRecallDetail();
				recallDetail.setMedicine(medicalOrdersProduct.getMedicine());
				recallDetail.setRecall(recall);
				recallDetail.setQuantity(medicalOrdersProduct.getQuantity());
				this.iBaseDao.saveObject(recallDetail);
			}
			
			MedicalOrdersWaitDelivery bean = (MedicalOrdersWaitDelivery) this.iBaseDao.getObject(MedicalOrdersWaitDelivery.class, ordersWaitDelivery.getId());
			MedicalOrdersAlreadyDelivery ordersAlreadyDelivery = new MedicalOrdersAlreadyDelivery();
			BeanUtils.copyProperties(bean, ordersAlreadyDelivery);
			ordersAlreadyDelivery.setExpressId(ordersWaitDelivery.getExpressId());
			ordersAlreadyDelivery.setExpressNo(ordersWaitDelivery.getExpressNo());
			ordersAlreadyDelivery.setDeliveryTime(new Date());
			ordersAlreadyDelivery.setStatus(SystemConfig.ORDERS_DELIVERY_TYPE);
			ordersAlreadyDelivery.setWarHouseNo(warehouse.getCode());
			this.iBaseDao.addObject(ordersAlreadyDelivery);
			ordersStorage.setDeliveryTime(new Date());
			ordersStorage.setExpressId(ordersAlreadyDelivery.getExpressId());
			ordersStorage.setExpressNo(ordersWaitDelivery.getExpressNo());
			ordersStorage.setWarehouseStatus(SystemConfig.ORDERS_DELIVERY_TYPE);
			// 发货后删除订单通过表中数据，防止订单被取消
			this.iBaseDao.updateHql("delete from MedicalOrdersPass a where a.id=? ", ordersAlreadyDelivery.getId());
			this.iBaseDao.deleteObject(bean);
			return "1";
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importDelivery(MedicalAdmin admin, File file) throws Exception {
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		int rightRow = 0;
	 	int errorRow = 0;
		// 循环导入
		while(rows.hasNext()){
			row = rows.next();
			if(row.getRowNum() == 0) continue;
			if(null != row.getCell(0) && null != row.getCell(1) && null != row.getCell(2)) {
				String ordersSerialNum = null;
				String LogisticsSerialNum = row.getCell(1).getStringCellValue().trim();
				String expressNo = null;
				String warHourseCode = row.getCell(3).getStringCellValue().trim();
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()) {
					ordersSerialNum = row.getCell(0).getStringCellValue().trim();
				} else {
					ordersSerialNum = String.format("%.0f",row.getCell(0).getNumericCellValue());
				}
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(2).getCellType()) {
					expressNo = row.getCell(2).getStringCellValue().trim();
				} else {
					expressNo = String.format("%.0f",row.getCell(2).getNumericCellValue());
				}
				if(StringUtils.isNotBlank(ordersSerialNum) && StringUtils.isNotBlank(LogisticsSerialNum) && StringUtils.isNotBlank(expressNo) && StringUtils.isNotBlank(warHourseCode)) {
					MedicalOrdersWaitDelivery ordersWaitDelivery = (MedicalOrdersWaitDelivery) this.iBaseDao.getObjectByHql("from MedicalOrdersWaitDelivery a where a.serialNum=? ", ordersSerialNum);
					if(null != ordersWaitDelivery) {
						MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ordersWaitDelivery.getId());
						if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStorage.getOrdersStatus())) {
							errorRow++;			// 订单已发货
						} else {
							
							// 获得订单产品
							@SuppressWarnings("unchecked")
							List<MedicalOrdersProduct> list = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersStorage.getId());
							// 判断库存
							boolean flag = true;
							for (MedicalOrdersProduct medicalOrdersProduct : list) {
								MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", warHourseCode, medicalOrdersProduct.getMedicine().getId());
								if(inStock == null || medicalOrdersProduct.getQuantity() > inStock.getQuantity()){
									errorRow++;			// 药品库存不足
									flag = false;
								}
							}
							
							if(flag){
								// 全部符合条件后减库存
								for (MedicalOrdersProduct medicalOrdersProduct : list) {
									MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", warHourseCode, medicalOrdersProduct.getMedicine().getId());
									inStock.setQuantity(inStock.getQuantity() - medicalOrdersProduct.getQuantity());
								}
								
								MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();	
								MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObjectByHql("from MedicalWarehouse a where 1=1 and a.hospitalId=? and a.code=? ", admin.getEnterpriseId(), warHourseCode.substring(1, "-".indexOf(warHourseCode)));
								// 生成出库单
								MedicalRecall recall = new MedicalRecall();
								recall.setSerialNum(SerialNumUtil.code());
								recall.setTitle("销售出库");
								recall.setRecallTime(new Date());
								recall.setStatus(SystemConfig.RECALL_STATUS_APPROVAL);
								recall.setWarehouse(warehouse);
								recall.setType(SystemConfig.RECALL_SELL_TYPE);
								recall.setWriteUser(user);
								recall.setHospitalId(user.getEnterpriseId());
								this.iBaseDao.saveObject(recall);
								
								for (MedicalOrdersProduct medicalOrdersProduct : list) {
									MedicalRecallDetail recallDetail = new MedicalRecallDetail();
									recallDetail.setMedicine(medicalOrdersProduct.getMedicine());
									recallDetail.setRecall(recall);
									recallDetail.setQuantity(medicalOrdersProduct.getQuantity());
									this.iBaseDao.saveObject(recallDetail);
								}
								
								MedicalOrdersAlreadyDelivery ordersAlreadyDelivery = new MedicalOrdersAlreadyDelivery();
								BeanUtils.copyProperties(ordersWaitDelivery, ordersAlreadyDelivery);
								MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObjectByHql("from MedicalLogistics a where 1=1 and a.hospitalId=? and a.serialNum=? ", admin.getEnterpriseId(), LogisticsSerialNum.substring(1, "-".indexOf(LogisticsSerialNum)));
								if(null != logistics) {
									ordersAlreadyDelivery.setExpressId(logistics.getId());
								} else {
									ordersAlreadyDelivery.setExpressId("");
								}
								ordersAlreadyDelivery.setExpressNo(expressNo);
								ordersAlreadyDelivery.setDeliveryTime(new Date());
								ordersAlreadyDelivery.setStatus(SystemConfig.ORDERS_DELIVERY_TYPE);
								ordersAlreadyDelivery.setWarHouseNo(warehouse.getCode());
								this.iBaseDao.addObject(ordersAlreadyDelivery);
								ordersStorage.setDeliveryTime(new Date());
								if(null != logistics) {
									ordersStorage.setExpressId(logistics.getId());
								}
								ordersStorage.setExpressNo(expressNo);
								ordersStorage.setWarehouseStatus(SystemConfig.ORDERS_DELIVERY_TYPE);
								this.iBaseDao.updateHql("delete from MedicalOrdersPass a where a.id=? ", ordersAlreadyDelivery.getId());
								this.iBaseDao.deleteObject(ordersWaitDelivery);
								rightRow++;
							}
							
						}
					} else {
						errorRow++;	// 订单不存在
					}
				} else {
					errorRow++;		// 列为空格
				}
			} else {				// 列为空
				errorRow++;
			}
		}
		String rr = rightRow + "";
		String er = errorRow + "";
		return rr + "," + er;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page findOrdersAlreadyDelivery(MedicalOrdersAlreadyDelivery ordersAlreadyDelivery, Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersAlreadyDelivery a where 1=1 and a.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersAlreadyDelivery a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(ordersAlreadyDelivery.getHospitalId());
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getSerialNum())) {
			countSql.append("and a.serialNum=? ");
			statusSql.append("and a.serialNum=? ");
			objectList.add(ordersAlreadyDelivery.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getExpressId())) {
			countSql.append("and a.expressId=? ");
			statusSql.append("and a.expressId=? ");
			objectList.add(ordersAlreadyDelivery.getExpressId());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getName())) {
			countSql.append("and a.name=? ");
			statusSql.append("and a.name=? ");
			objectList.add(ordersAlreadyDelivery.getName());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getZipCode())) {
			countSql.append("and a.zipCode=? ");
			statusSql.append("and a.zipCode=? ");
			objectList.add(ordersAlreadyDelivery.getZipCode());
		}
		
		if(null != ordersAlreadyDelivery.getStartTime()){
			countSql.append("and a.checkTime>=? ");
			statusSql.append("and a.checkTime>=? ");
			objectList.add(ordersAlreadyDelivery.getStartTime());
		}
		
		if(null != ordersAlreadyDelivery.getEndTime()){
			countSql.append("and a.checkTime<=? ");
			statusSql.append("and a.checkTime<=? ");
			objectList.add(ordersAlreadyDelivery.getEndTime());
		}
		
		if(null != ordersAlreadyDelivery.getOrderTime()){
			countSql.append("and a.createTime>=? ");
			statusSql.append("and a.createTime>=? ");
			objectList.add(ordersAlreadyDelivery.getOrderTime());
		}
		
		if(null != ordersAlreadyDelivery.getOrderTime2()){
			countSql.append("and a.createTime<=? ");
			statusSql.append("and a.createTime<=? ");
			objectList.add(ordersAlreadyDelivery.getOrderTime2());
		}
		
		//排序
		String[] sortField =(String[]) map.get("sortField");
		String[] sortOrder =(String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersAlreadyDelivery> list = new ArrayList<MedicalOrdersAlreadyDelivery>();
		for(Object obj : page.getResult()) {
			ordersAlreadyDelivery = (MedicalOrdersAlreadyDelivery) obj;
			// 审核人
			MedicalAdmin checkUser = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, ordersAlreadyDelivery.getCheckUserId());
			ordersAlreadyDelivery.setCheckUserName(checkUser.getName());
			// 订单详细
			List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersAlreadyDelivery.getId());
			String ordersDetail = "";
			for(MedicalOrdersProduct ordersProduct : ordersProductList) {
				ordersDetail += ordersProduct.getMedicine().getName() + ", ";
			}
			ordersAlreadyDelivery.setOrdersDetail(ordersDetail);
			
			list.add(ordersAlreadyDelivery);
		}
		page.setResult(list);
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void ordersRetreat(String ordersIds) {
		String ordersId[] = ordersIds.split(",");
		for(String oId : ordersId) {
			MedicalOrdersAlreadyDelivery ordersAlreadyDelivery = (MedicalOrdersAlreadyDelivery) this.iBaseDao.getObject(MedicalOrdersAlreadyDelivery.class, oId);
			
			// 获得订单产品
			@SuppressWarnings("unchecked")
			List<MedicalOrdersProduct> list = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersAlreadyDelivery.getId());
			
			// 增加库存
			for (MedicalOrdersProduct medicalOrdersProduct : list) {
				MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.code=? and a.medicine.id=? ", ordersAlreadyDelivery.getWarHouseNo(), medicalOrdersProduct.getMedicine().getId());
				inStock.setQuantity(inStock.getQuantity() + medicalOrdersProduct.getQuantity());
			}
			
			MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();	
			MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObjectByHql("from MedicalWarehouse a where a.hospitalId=? and a.code=? ",user.getEnterpriseId(), ordersAlreadyDelivery.getWarHouseNo());
			// 生成入库单
			MedicalStorage storage = new MedicalStorage();
			storage.setSerialNum(SerialNumUtil.code());
			storage.setTitle("退单入库");
			storage.setStorageTime(new Date());
			storage.setStatus(SystemConfig.STORAGE_STATUS_APPROVAL);
			storage.setWarehouse(warehouse);
			storage.setType(SystemConfig.STORAGE_RETREAT_TYPE);
			storage.setWriteUser(user);
			storage.setAuditUser(user);
			storage.setHospitalId(user.getEnterpriseId());
			this.iBaseDao.saveObject(storage);
			
			for (MedicalOrdersProduct medicalOrdersProduct : list) {
				MedicalStorageDetail storageDetail = new MedicalStorageDetail();
				storageDetail.setMedicine(medicalOrdersProduct.getMedicine());
				storageDetail.setStorage(storage);
				storageDetail.setQuantity(medicalOrdersProduct.getQuantity());
				this.iBaseDao.saveObject(storageDetail);
			}
			
			MedicalOrdersRetreat ordersRetreat = new MedicalOrdersRetreat();
			BeanUtils.copyProperties(ordersAlreadyDelivery, ordersRetreat);
			this.iBaseDao.addObject(ordersRetreat);
			this.iBaseDao.deleteObject(ordersAlreadyDelivery);
			MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, oId);
			ordersStorage.setWarehouseStatus(SystemConfig.ORDERS_RETREAT_TYPE);
			// 退单处理客户积分、红包、余额
			MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
			customer.setMoney(customer.getMoney().add(ordersStorage.getUseMoney()));			// 加回余额
			customer.setIntegral(customer.getIntegral() + ordersStorage.getUsePoints());		// 积分
			customer.setRedEnvelopes(customer.getRedEnvelopes() + ordersStorage.getUseRed() - ordersStorage.getRed());	// 红包
		}
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void ordersNetRetreat(String ordersIds) {
		String ordersId[] = ordersIds.split("-");
		for (String oid : ordersId) {
			this.iBaseDao.updateHql("update MedicalOrdersAlreadyDelivery a set a.status=? where a.id=? ", SystemConfig.ORDERS_NET_RETREAT_TYPE,oid);
			this.iBaseDao.updateHql("update MedicalOrdersStorage a set a.warehouseStatus=? where a.id=? ", SystemConfig.ORDERS_NET_RETREAT_TYPE,oid);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importRetreat(File file) throws Exception {
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		int rightRow = 0;
	 	int errorRow = 0;
		// 循环导入
		while(rows.hasNext()){
			row = rows.next();
			if(row.getRowNum() == 0) continue;
			if(null != row.getCell(0)) {
				String expressNo = null;
				
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()) {
					expressNo = row.getCell(0).getStringCellValue().trim();
				} else {
					expressNo = String.format("%.0f",row.getCell(0).getNumericCellValue());
				}
				
				if(StringUtils.isNotBlank(expressNo)) {
					MedicalOrdersAlreadyDelivery ordersAlreadyDelivery = (MedicalOrdersAlreadyDelivery) this.iBaseDao.getObjectByHql("from MedicalOrdersAlreadyDelivery a where a.expressNo=? ", expressNo);
					if(null != ordersAlreadyDelivery) {
						// 获得订单产品
						@SuppressWarnings("unchecked")
						List<MedicalOrdersProduct> list = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersAlreadyDelivery.getId());
						
						// 增加库存
						for (MedicalOrdersProduct medicalOrdersProduct : list) {
							MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.code=? and a.medicine.id=? ", ordersAlreadyDelivery.getWarHouseNo(), medicalOrdersProduct.getMedicine().getId());
							inStock.setQuantity(inStock.getQuantity() + medicalOrdersProduct.getQuantity());
						}
						
						MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();	
						MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObjectByHql("from MedicalWarehouse a where a.hospitalId=? and a.code=? ",user.getEnterpriseId(), ordersAlreadyDelivery.getWarHouseNo());
						// 生成入库单
						MedicalStorage storage = new MedicalStorage();
						storage.setSerialNum(SerialNumUtil.code());
						storage.setTitle("退单入库");
						storage.setStorageTime(new Date());
						storage.setStatus(SystemConfig.STORAGE_STATUS_APPROVAL);
						storage.setWarehouse(warehouse);
						storage.setType(SystemConfig.STORAGE_RETREAT_TYPE);
						storage.setWriteUser(user);
						storage.setAuditUser(user);
						storage.setHospitalId(user.getEnterpriseId());
						this.iBaseDao.saveObject(storage);
						
						for (MedicalOrdersProduct medicalOrdersProduct : list) {
							MedicalStorageDetail storageDetail = new MedicalStorageDetail();
							storageDetail.setMedicine(medicalOrdersProduct.getMedicine());
							storageDetail.setStorage(storage);
							storageDetail.setQuantity(medicalOrdersProduct.getQuantity());
							this.iBaseDao.saveObject(storageDetail);
						}
						
						MedicalOrdersRetreat ordersRetreat = new MedicalOrdersRetreat();
						BeanUtils.copyProperties(ordersAlreadyDelivery, ordersRetreat);
						this.iBaseDao.addObject(ordersRetreat);
						MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ordersAlreadyDelivery.getId());
						ordersStorage.setWarehouseStatus(SystemConfig.ORDERS_RETREAT_TYPE);
						this.iBaseDao.deleteObject(ordersAlreadyDelivery);
						
						// 退单处理客户积分、红包、余额
						MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
						customer.setMoney(customer.getMoney().add(ordersStorage.getUseMoney()));			// 加回余额
						customer.setIntegral(customer.getIntegral() + ordersStorage.getUsePoints());		// 积分
						customer.setRedEnvelopes(customer.getRedEnvelopes() + ordersStorage.getUseRed() - ordersStorage.getRed());	// 红包
						
						rightRow++;
					} else {
						errorRow++;		// 订单为空
					}
				} else {
					errorRow++;		// 列中为空格
				}
			} else {				// 列为空
				errorRow++;
			}
		}
		String rr = rightRow + "";
		String er = errorRow + "";
		return rr + "," + er;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public Map<String, Object> ordersSign(MedicalAdmin admin, String ordersIds) {
		String ordersId[] = ordersIds.split(",");
		Map<String, Object> temp = new HashMap<String, Object>();
		List<MedicalOrdersSign> tempList = new ArrayList<MedicalOrdersSign>();
		MedicalEnterprise enterprise = (MedicalEnterprise) this.iBaseDao.getObject(MedicalEnterprise.class, admin.getEnterpriseId());
		for(String oId : ordersId) {
			MedicalOrdersAlreadyDelivery ordersAlreadyDelivery = (MedicalOrdersAlreadyDelivery) this.iBaseDao.getObject(MedicalOrdersAlreadyDelivery.class, oId);
			MedicalOrdersSign ordersSign = new MedicalOrdersSign();
			BeanUtils.copyProperties(ordersAlreadyDelivery, ordersSign);
			ordersSign.setSignTime(new Date());
			this.iBaseDao.addObject(ordersSign);
			tempList.add(ordersSign);
			MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, oId);
			ordersStorage.setWarehouseStatus(SystemConfig.ORDERS_SIGN_TYPE);
			ordersStorage.setSignTime(new Date());
			// 货到付款签收后发给财务对账。
			if(!SystemConfig.ORDERS_DOWNLINE_PAY_TYPE.equals(ordersSign.getPayType())) {
				if(SystemConfig.FINANCIAL_HAS_RECONCILIATION.equals(enterprise.getNeedStatus()) || SystemConfig.FINANCIAL_RECON_BACK_SECTION.equals(enterprise.getNeedStatus())) {
					MedicalFinancialOrders financialOrders = new MedicalFinancialOrders();
					BeanUtils.copyProperties(ordersSign, financialOrders);
					this.iBaseDao.addObject(financialOrders);
					ordersStorage.setFinancialStatus(SystemConfig.BE_RECONCILED);		// 对账
					
				} else if(SystemConfig.FINANCIAL_HAS_BACK_SECTION.equals(enterprise.getNeedStatus())) {
					MedicalOrderReconciliation orderReconciliation = new MedicalOrderReconciliation();
					BeanUtils.copyProperties(ordersSign, orderReconciliation);
					orderReconciliation.setWarehouseStatus(SystemConfig.ORDERS_SIGN_TYPE);
					orderReconciliation.setClaimStatus(SystemConfig.UN_CLAIMED);		// 未认领状态
					this.iBaseDao.addObject(orderReconciliation);
					ordersStorage.setFinancialStatus(SystemConfig.UN_CLAIMED);			// 未认领
				} else {
					// 无财务系统直接结束
				}
			} else {	// 线下汇款、更改财务仓储状态
				if(!SystemConfig.FINANCIAL_HAS_BACK_SECTION.equals(enterprise.getNeedStatus()) && !SystemConfig.FINANCIAL_SYS_NO.equals(enterprise.getNeedStatus())) {
					MedicalOrderReconciliation orderReconciliation = (MedicalOrderReconciliation) this.iBaseDao.getObject(MedicalOrderReconciliation.class, oId);
					orderReconciliation.setWarehouseStatus(SystemConfig.ORDERS_SIGN_TYPE);
					orderReconciliation.setClaimStatus(SystemConfig.HAS_CLAIMED);
				}
			}
			// 签收后计算客户余额、积分
			MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
			BigDecimal receiAndFreight = ordersStorage.getReceivable().add(ordersStorage.getFreight());
			// 客户多付金额(实收 > 应收+运费)
			if(ordersStorage.getAmountPaid().doubleValue() > receiAndFreight.doubleValue()) {
				BigDecimal surplusMoney = ordersStorage.getAmountPaid().subtract(receiAndFreight);
				customer.setMoney(customer.getMoney().add(surplusMoney));
			}
			customer.setIntegral(customer.getIntegral() + ordersStorage.getPoints());	// 加上本单积分
			
			this.iBaseDao.deleteObject(ordersAlreadyDelivery);
		}
		temp.put("ordersSignList", tempList);
		return temp;
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void ordersNetSign(MedicalAdmin admin, String ordersIds) {
		String ordersId[] = ordersIds.split("-");
		for (String orderId : ordersId) {
			this.iBaseDao.updateHql("update MedicalOrdersAlreadyDelivery a set a.status=? where a.id=? ", SystemConfig.ORDERS_NET_SIGN_TYPE,orderId);
			this.iBaseDao.updateHql("update MedicalOrdersStorage a set a.warehouseStatus=? where a.id=? ", SystemConfig.ORDERS_NET_SIGN_TYPE,orderId);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public Map<String, Object> importSign(File file) throws Exception{
		Map<String, Object> temp = new HashMap<String, Object>();
		List<MedicalOrdersSign> tempList = new ArrayList<MedicalOrdersSign>();
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		int rightRow = 0;
	 	int errorRow = 0;
	 	StringBuilder errorInfo = new StringBuilder();
		// 循环导入
		while(rows.hasNext()){
			row = rows.next();
			if(row.getRowNum() == 0) continue;
			if(null != row.getCell(0)) {
				String expressNo = null;
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()) {
					expressNo = row.getCell(0).getStringCellValue().trim();
				} else {
					expressNo = String.format("%.0f",row.getCell(0).getNumericCellValue());
				}
				
				if(StringUtils.isNotBlank(expressNo)) {
					MedicalOrdersAlreadyDelivery ordersAlreadyDelivery = (MedicalOrdersAlreadyDelivery) this.iBaseDao.getObjectByHql("from MedicalOrdersAlreadyDelivery a where 1=1 and a.expressNo=? ", expressNo);
					if(null != ordersAlreadyDelivery) {
						MedicalOrdersSign ordersSign = new MedicalOrdersSign();
						BeanUtils.copyProperties(ordersAlreadyDelivery, ordersSign);
						ordersSign.setSignTime(new Date());
						this.iBaseDao.addObject(ordersSign);
						tempList.add(ordersSign);
						
						MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ordersAlreadyDelivery.getId());
						ordersStorage.setWarehouseStatus(SystemConfig.ORDERS_SIGN_TYPE);
						ordersStorage.setSignTime(new Date());
						// 只有线上付款和快递代收费签收后发给财务对账。
						if(!SystemConfig.ORDERS_DOWNLINE_PAY_TYPE.equals(ordersSign.getPayType())) {
							MedicalFinancialOrders financialOrders = new MedicalFinancialOrders();
							BeanUtils.copyProperties(ordersAlreadyDelivery, financialOrders);
							this.iBaseDao.addObject(financialOrders);
						} else {
							MedicalOrderReconciliation orderReconciliation = (MedicalOrderReconciliation) this.iBaseDao.getObject(MedicalOrderReconciliation.class, ordersAlreadyDelivery.getId());
							orderReconciliation.setWarehouseStatus(SystemConfig.ORDERS_SIGN_TYPE);
						}
						this.iBaseDao.deleteObject(ordersAlreadyDelivery);
						
						// 签收后计算客户余额、积分
						MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
						BigDecimal receiAndFreight = ordersStorage.getReceivable().add(ordersStorage.getFreight());
						// 客户多付金额(实收 > 应收+运费)
						if(ordersStorage.getAmountPaid().doubleValue() > receiAndFreight.doubleValue()) {
							BigDecimal surplusMoney = ordersStorage.getAmountPaid().subtract(receiAndFreight);
							customer.setMoney(customer.getMoney().add(surplusMoney));
						}
						customer.setIntegral(customer.getIntegral() + ordersStorage.getPoints());	// 加上本单积分
						
						rightRow++;
					} else {
						errorRow++;		// 订单为空
						errorInfo.append(expressNo+",");
					}
				} else {
					errorRow++;		// 列中为空格
					errorInfo.append(expressNo+",");
				}
			} else {				// 列为空
				errorRow++;
			}
		}
		temp.put("ordersSignList", tempList);
		String info = "导入成功："+rightRow + "条,导入失败："+errorRow+"条,失败快递单号："+errorInfo.toString();
		temp.put("info", info);
		return temp;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page findAllOrdersByPage(MedicalOrdersStorage ordersStorage, Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersStorage a where 1=1 and a.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(ordersStorage.getHospitalId());
		
		if(StringUtils.isNotBlank(ordersStorage.getSerialNum())) {
			countSql.append("and a.serialNum=? ");
			statusSql.append("and a.serialNum=? ");
			objectList.add(ordersStorage.getSerialNum());
		}

		if(StringUtils.isNotBlank(ordersStorage.getExpressId())) {
			countSql.append("and a.expressId=? ");
			statusSql.append("and a.expressId=? ");
			objectList.add(ordersStorage.getExpressId());
		}

		if(StringUtils.isNotBlank(ordersStorage.getName())) {
			countSql.append("and a.name=? ");
			statusSql.append("and a.name=? ");
			objectList.add(ordersStorage.getName());
		}

		if(StringUtils.isNotBlank(ordersStorage.getZipCode())) {
			countSql.append("and a.zipCode=? ");
			statusSql.append("and a.zipCode=? ");
			objectList.add(ordersStorage.getZipCode());
		}

		if(null != ordersStorage.getStartTime()){
			countSql.append("and a.checkTime>=? ");
			statusSql.append("and a.checkTime>=? ");
			objectList.add(ordersStorage.getStartTime());
		}

		if(null != ordersStorage.getEndTime()){
			countSql.append("and a.checkTime<=? ");
			statusSql.append("and a.checkTime<=? ");
			objectList.add(ordersStorage.getEndTime());
		}

		if(null != ordersStorage.getOrderTime()){
			countSql.append("and a.createTime>=? ");
			statusSql.append("and a.createTime>=? ");
			objectList.add(ordersStorage.getOrderTime());
		}

		if(null != ordersStorage.getOrderTime2()){
			countSql.append("and a.createTime<=? ");
			statusSql.append("and a.createTime<=? ");
			objectList.add(ordersStorage.getOrderTime2());
		}
		
		if(StringUtils.isNotBlank(ordersStorage.getKeyType()) && StringUtils.isNotBlank(ordersStorage.getStatus()) ) {
			if(ordersStorage.getKeyType().equals(SystemConfig.CM_SYSTEM_TYPE)) {
				countSql.append(" and a.callStatus = ?");
				statusSql.append(" and a.callStatus = ?");
				objectList.add(ordersStorage.getStatus());
			}
			
			if(ordersStorage.getKeyType().equals(SystemConfig.OM_SYSTEM_TYPE)) {
				countSql.append(" and a.ordersStatus = ?");
				statusSql.append(" and a.ordersStatus = ?");
				objectList.add(ordersStorage.getStatus());
			}
			
			if(ordersStorage.getKeyType().equals(SystemConfig.WM_SYSTEM_TYPE)) {
				countSql.append(" and a.warehouseStatus = ?");
				statusSql.append(" and a.warehouseStatus = ?");
				objectList.add(ordersStorage.getStatus());
			}
			
			if(ordersStorage.getKeyType().equals(SystemConfig.FM_SYSTEM_TYPE)) {
				countSql.append(" and a.financialStatus = ?");
				statusSql.append(" and a.financialStatus = ?");
				objectList.add(ordersStorage.getStatus());
			}
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils .isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])){
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersStorage> list = new ArrayList<MedicalOrdersStorage>();
		for(Object obj : page.getResult()) {
			ordersStorage = (MedicalOrdersStorage) obj;
			// 订单详细
			List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersStorage.getId());
			String ordersDetail = "";
			for(MedicalOrdersProduct ordersProduct : ordersProductList) {
				String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
				ordersDetail += product + ";";
			}
			
			MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, ordersStorage.getExpressId());
			if(null != logistics) {
				ordersStorage.setExpressName(logistics.getName());
			}
			ordersStorage.setOrdersDetail(ordersDetail);
			
			list.add(ordersStorage);
		}
		page.setResult(list);
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalLogistics> findLogisticsList(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalLogistics a where a.hospitalId=? ", null, admin.getEnterpriseId());
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void editLogistics(MedicalOrdersAlreadyDelivery ordersAlreadyDelivery) {
		String ordersId[] = ordersAlreadyDelivery.getId().split(",");
		for(String oId : ordersId) {
			this.iBaseDao.updateHql("update MedicalOrdersAlreadyDelivery a set a.expressId=? where a.id=? ", ordersAlreadyDelivery.getExpressId(), oId);
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportOrdersCurrent(String ids) throws Exception {
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.DELIVERY_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		if(StringUtils.isNotBlank(ids)){
			String [] ordersIds =  ids.split(",");
			for(int j=0;j<ordersIds.length;j++){
				// 获得记录
				MedicalOrdersWaitDelivery order = (MedicalOrdersWaitDelivery) this.iBaseDao.getObject(MedicalOrdersWaitDelivery.class, ordersIds[j]);
				Row currentRow = sheet.createRow(j+2);
				if(null != order){
					currentRow.createCell(0).setCellValue(order.getSerialNum());
					currentRow.createCell(1).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(2).setCellValue(order.getName());
					// 订单详细
					List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
					String ordersDetail = "";
					for(MedicalOrdersProduct ordersProduct : ordersProductList) {
						String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
						ordersDetail += product + ";";
					}
					if(StringUtils.isNotBlank(ordersDetail)){
						currentRow.createCell(3).setCellValue(ordersDetail);
					}
					currentRow.createCell(4).setCellValue(order.getAmountPaid().toString());
					currentRow.createCell(5).setCellValue(order.getContactNo());
					currentRow.createCell(6).setCellValue(order.getProvince().getpName());
					currentRow.createCell(7).setCellValue(order.getCity().getcName());
					currentRow.createCell(8).setCellValue(order.getDistrict().getdName());
					currentRow.createCell(9).setCellValue(order.getAddress());
					currentRow.createCell(10).setCellValue(order.getZipCode());
					String payType = null;
					if(SystemConfig.ORDERS_DOWNLINE_PAY_TYPE.equals(order.getPayType())) {
						payType = "线下汇款";
					} else if(SystemConfig.ORDERS_GOODSTO_PAY_TYPE.equals(order.getPayType())) {
						payType = "货到付款";
					}
					if(StringUtils.isNotBlank(payType)) {
						currentRow.createCell(11).setCellValue(payType);
					}
					String invoice = null;
					if(SystemConfig.ORDERS_HAVE_INVOICE.equals(order.getInvoice())) {
						invoice = "是";
					} else {
						invoice = "否";
					}
					if(StringUtils.isNotBlank(invoice)) {
						currentRow.createCell(12).setCellValue(invoice);
					}
					currentRow.createCell(13).setCellValue(order.getOrdersRemark());
				}
			}
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportOrdersAll(MedicalOrdersWaitDelivery ordersWaitDelivery) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersWaitDelivery a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getSerialNum())) {
			statusSql.append("and a.serialNum=? ");
			objectList.add(ordersWaitDelivery.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getExpressId())) {
			statusSql.append("and a.expressId=? ");
			objectList.add(ordersWaitDelivery.getExpressId());
		}
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getName())) {
			statusSql.append("and a.name=? ");
			objectList.add(ordersWaitDelivery.getName());
		}
		
		if(StringUtils.isNotBlank(ordersWaitDelivery.getZipCode())) {
			statusSql.append("and a.zipCode=? ");
			objectList.add(ordersWaitDelivery.getZipCode());
		}
		
		if(null != ordersWaitDelivery.getStartTime()){
			statusSql.append("and a.checkTime>=? ");
			objectList.add(ordersWaitDelivery.getStartTime());
		}
		
		if(null != ordersWaitDelivery.getEndTime()){
			statusSql.append("and a.checkTime<=? ");
			objectList.add(ordersWaitDelivery.getEndTime());
		}
		
		if(null != ordersWaitDelivery.getOrderTime()){
			statusSql.append("and a.createTime>=? ");
			objectList.add(ordersWaitDelivery.getOrderTime());
		}
		
		if(null != ordersWaitDelivery.getOrderTime2()){
			statusSql.append("and a.createTime<=? ");
			objectList.add(ordersWaitDelivery.getOrderTime2());
		}
		
		List<MedicalOrdersWaitDelivery> list = this.iBaseDao.getBySql(statusSql.toString() ,null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.DELIVERY_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersWaitDelivery order = list.get(j);
			Row currentRow = sheet.createRow(j+2);
			if(null != order){
				currentRow.createCell(0).setCellValue(order.getSerialNum());
				currentRow.createCell(1).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(2).setCellValue(order.getName());
				// 订单详细
				List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
				String ordersDetail = "";
				for(MedicalOrdersProduct ordersProduct : ordersProductList) {
					String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
					ordersDetail += product + ";";
				}
				if(StringUtils.isNotBlank(ordersDetail)){
					currentRow.createCell(3).setCellValue(ordersDetail);
				}
				currentRow.createCell(4).setCellValue(order.getAmountPaid().toString());
				currentRow.createCell(5).setCellValue(order.getContactNo());
				currentRow.createCell(6).setCellValue(order.getProvince().getpName());
				currentRow.createCell(7).setCellValue(order.getCity().getcName());
				currentRow.createCell(8).setCellValue(order.getDistrict().getdName());
				currentRow.createCell(9).setCellValue(order.getAddress());
				currentRow.createCell(10).setCellValue(order.getZipCode());
				String payType = null;
				if(SystemConfig.ORDERS_DOWNLINE_PAY_TYPE.equals(order.getPayType())) {
					payType = "线下汇款";
				} else if(SystemConfig.ORDERS_GOODSTO_PAY_TYPE.equals(order.getPayType())) {
					payType = "货到付款";
				}
				if(StringUtils.isNotBlank(payType)) {
					currentRow.createCell(11).setCellValue(payType);
				}
				String invoice = null;
				if(SystemConfig.ORDERS_HAVE_INVOICE.equals(order.getInvoice())) {
					invoice = "是";
				} else {
					invoice = "否";
				}
				if(StringUtils.isNotBlank(invoice)) {
					currentRow.createCell(12).setCellValue(invoice);
				}
				currentRow.createCell(13).setCellValue(order.getOrdersRemark());
			}
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportSignOrdersCurrent(String ids) throws Exception {
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.SIGN_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		if(StringUtils.isNotBlank(ids)){
			String [] ordersIds =  ids.split(",");
			for(int j=0;j<ordersIds.length;j++){
				// 获得记录
				MedicalOrdersAlreadyDelivery order = (MedicalOrdersAlreadyDelivery) this.iBaseDao.getObject(MedicalOrdersAlreadyDelivery.class, ordersIds[j]);
				Row currentRow = sheet.createRow(j + 1);
				if(null != order){
					currentRow.createCell(0).setCellValue(order.getSerialNum());
					String status = null;
					if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(order.getStatus())){
						status = "订单发货";
					}else if(SystemConfig.ORDERS_NET_SIGN_TYPE.equals(order.getStatus())){
						status = "订单网签";
					}else if(SystemConfig.ORDERS_NET_RETREAT_TYPE.equals(order.getStatus())){
						status = "订单网退";
					}
					if(StringUtils.isNotBlank(status)) {
						currentRow.createCell(1).setCellValue(status);
					}
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(order.getName());
					currentRow.createCell(4).setCellValue(order.getContactNo());
					currentRow.createCell(5).setCellValue(order.getAmountPaid().toString());
					currentRow.createCell(6).setCellValue(order.getProvince().getpName());
					currentRow.createCell(7).setCellValue(order.getCity().getcName());
					currentRow.createCell(8).setCellValue(order.getDistrict().getdName());
					currentRow.createCell(9).setCellValue(order.getAddress());
					currentRow.createCell(10).setCellValue(order.getExpressNo());
					currentRow.createCell(11).setCellValue(DateFormatUtils.format(order.getDeliveryTime(),"yyyy-MM-dd HH:mm:ss"));
					// 订单详细
					List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
					String ordersDetail = "";
					for(MedicalOrdersProduct ordersProduct : ordersProductList) {
						String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
						ordersDetail += product + ";";
					}
					if(StringUtils.isNotBlank(ordersDetail)){
						currentRow.createCell(12).setCellValue(ordersDetail);
					}
				}
			}
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportSignOrdersAll(MedicalOrdersAlreadyDelivery ordersAlreadyDelivery) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersAlreadyDelivery a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getSerialNum())) {
			statusSql.append("and a.serialNum=? ");
			objectList.add(ordersAlreadyDelivery.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getExpressId())) {
			statusSql.append("and a.expressId=? ");
			objectList.add(ordersAlreadyDelivery.getExpressId());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getName())) {
			statusSql.append("and a.name=? ");
			objectList.add(ordersAlreadyDelivery.getName());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getZipCode())) {
			statusSql.append("and a.zipCode=? ");
			objectList.add(ordersAlreadyDelivery.getZipCode());
		}
		
		if(null != ordersAlreadyDelivery.getStartTime()){
			statusSql.append("and a.checkTime>=? ");
			objectList.add(ordersAlreadyDelivery.getStartTime());
		}
		
		if(null != ordersAlreadyDelivery.getEndTime()){
			statusSql.append("and a.checkTime<=? ");
			objectList.add(ordersAlreadyDelivery.getEndTime());
		}
		
		if(null != ordersAlreadyDelivery.getOrderTime()){
			statusSql.append("and a.createTime>=? ");
			objectList.add(ordersAlreadyDelivery.getOrderTime());
		}
		
		if(null != ordersAlreadyDelivery.getOrderTime2()){
			statusSql.append("and a.createTime<=? ");
			objectList.add(ordersAlreadyDelivery.getOrderTime2());
		}
		
		List<MedicalOrdersAlreadyDelivery> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.SIGN_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersAlreadyDelivery order = list.get(j);
			Row currentRow = sheet.createRow(j + 1);
			if(null != order){
				currentRow.createCell(0).setCellValue(order.getSerialNum());
				String status = null;
				if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(order.getStatus())){
					status = "订单发货";
				}else if(SystemConfig.ORDERS_NET_SIGN_TYPE.equals(order.getStatus())){
					status = "订单网签";
				}else if(SystemConfig.ORDERS_NET_RETREAT_TYPE.equals(order.getStatus())){
					status = "订单网退";
				}
				if(StringUtils.isNotBlank(status)) {
					currentRow.createCell(1).setCellValue(status);
				}
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(order.getName());
				currentRow.createCell(4).setCellValue(order.getContactNo());
				currentRow.createCell(5).setCellValue(order.getAmountPaid().toString());
				currentRow.createCell(6).setCellValue(order.getProvince().getpName());
				currentRow.createCell(7).setCellValue(order.getCity().getcName());
				currentRow.createCell(8).setCellValue(order.getDistrict().getdName());
				currentRow.createCell(9).setCellValue(order.getAddress());
				currentRow.createCell(10).setCellValue(order.getExpressNo());
				currentRow.createCell(11).setCellValue(DateFormatUtils.format(order.getDeliveryTime(),"yyyy-MM-dd HH:mm:ss"));
				// 订单详细
				List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
				String ordersDetail = "";
				for(MedicalOrdersProduct ordersProduct : ordersProductList) {
					String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
					ordersDetail += product + ";";
				}
				if(StringUtils.isNotBlank(ordersDetail)){
					currentRow.createCell(12).setCellValue(ordersDetail);
				}
			}
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportRetreatOrdersCurrent(String ids) throws Exception {
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.RETREAT_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		if(StringUtils.isNotBlank(ids)){
			String [] ordersIds =  ids.split(",");
			for(int j=0;j<ordersIds.length;j++){
				// 获得记录
				MedicalOrdersAlreadyDelivery order = (MedicalOrdersAlreadyDelivery) this.iBaseDao.getObject(MedicalOrdersAlreadyDelivery.class, ordersIds[j]);
				Row currentRow = sheet.createRow(j + 1);
				if(null != order){
					currentRow.createCell(0).setCellValue(order.getSerialNum());
					String status = null;
					if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(order.getStatus())){
						status = "订单发货";
					}else if(SystemConfig.ORDERS_NET_SIGN_TYPE.equals(order.getStatus())){
						status = "订单网签";
					}else if(SystemConfig.ORDERS_NET_RETREAT_TYPE.equals(order.getStatus())){
						status = "订单网退";
					}
					if(StringUtils.isNotBlank(status)) {
						currentRow.createCell(1).setCellValue(status);
					}
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(order.getName());
					// 订单详细
					List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
					String ordersDetail = "";
					for(MedicalOrdersProduct ordersProduct : ordersProductList) {
						String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
						ordersDetail += product + ";";
					}
					if(StringUtils.isNotBlank(ordersDetail)){
						currentRow.createCell(4).setCellValue(ordersDetail);
					}
					currentRow.createCell(5).setCellValue(order.getAmountPaid().toString());
					currentRow.createCell(6).setCellValue(order.getProvince().getpName());
					currentRow.createCell(7).setCellValue(order.getCity().getcName());
					currentRow.createCell(8).setCellValue(order.getDistrict().getdName());
					currentRow.createCell(9).setCellValue(order.getAddress());
					currentRow.createCell(10).setCellValue(order.getZipCode());
					MedicalAdmin checkUser = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, order.getCheckUserId());
					currentRow.createCell(11).setCellValue(checkUser.getName());
					currentRow.createCell(12).setCellValue(DateFormatUtils.format(order.getCheckTime(),"yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportRetreatOrdersAll(MedicalOrdersAlreadyDelivery ordersAlreadyDelivery) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersAlreadyDelivery a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getSerialNum())) {
			statusSql.append("and a.serialNum=? ");
			objectList.add(ordersAlreadyDelivery.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getExpressId())) {
			statusSql.append("and a.expressId=? ");
			objectList.add(ordersAlreadyDelivery.getExpressId());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getName())) {
			statusSql.append("and a.name=? ");
			objectList.add(ordersAlreadyDelivery.getName());
		}
		
		if(StringUtils.isNotBlank(ordersAlreadyDelivery.getZipCode())) {
			statusSql.append("and a.zipCode=? ");
			objectList.add(ordersAlreadyDelivery.getZipCode());
		}
		
		if(null != ordersAlreadyDelivery.getStartTime()){
			statusSql.append("and a.checkTime>=? ");
			objectList.add(ordersAlreadyDelivery.getStartTime());
		}
		
		if(null != ordersAlreadyDelivery.getEndTime()){
			statusSql.append("and a.checkTime<=? ");
			objectList.add(ordersAlreadyDelivery.getEndTime());
		}
		
		if(null != ordersAlreadyDelivery.getOrderTime()){
			statusSql.append("and a.createTime>=? ");
			objectList.add(ordersAlreadyDelivery.getOrderTime());
		}
		
		if(null != ordersAlreadyDelivery.getOrderTime2()){
			statusSql.append("and a.createTime<=? ");
			objectList.add(ordersAlreadyDelivery.getOrderTime2());
		}
		
		List<MedicalOrdersAlreadyDelivery> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.RETREAT_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersAlreadyDelivery order = list.get(j);
				Row currentRow = sheet.createRow(j + 1);
				if(null != order){
					currentRow.createCell(0).setCellValue(order.getSerialNum());
					String status = null;
					if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(order.getStatus())){
						status = "订单发货";
					}else if(SystemConfig.ORDERS_NET_SIGN_TYPE.equals(order.getStatus())){
						status = "订单网签";
					}else if(SystemConfig.ORDERS_NET_RETREAT_TYPE.equals(order.getStatus())){
						status = "订单网退";
					}
					if(StringUtils.isNotBlank(status)) {
						currentRow.createCell(1).setCellValue(status);
					}
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(order.getName());
					// 订单详细
					List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
					String ordersDetail = "";
					for(MedicalOrdersProduct ordersProduct : ordersProductList) {
						String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
						ordersDetail += product + ";";
					}
					if(StringUtils.isNotBlank(ordersDetail)){
						currentRow.createCell(4).setCellValue(ordersDetail);
					}
					currentRow.createCell(5).setCellValue(order.getAmountPaid().toString());
					currentRow.createCell(6).setCellValue(order.getProvince().getpName());
					currentRow.createCell(7).setCellValue(order.getCity().getcName());
					currentRow.createCell(8).setCellValue(order.getDistrict().getdName());
					currentRow.createCell(9).setCellValue(order.getAddress());
					currentRow.createCell(10).setCellValue(order.getZipCode());
					MedicalAdmin checkUser = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, order.getCheckUserId());
					currentRow.createCell(11).setCellValue(checkUser.getName());
					currentRow.createCell(12).setCellValue(DateFormatUtils.format(order.getCheckTime(),"yyyy-MM-dd HH:mm:ss"));
				}
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportQueryOrdersCurrent(String ids) throws Exception {
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.QUERY_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		
		Sheet sheet = workbook.getSheetAt(0);
		if(StringUtils.isNotBlank(ids)){
			String [] ordersIds =  ids.split(",");
			for(int j=0;j<ordersIds.length;j++){
				// 获得记录
				MedicalOrdersStorage order = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ordersIds[j]);
				Row currentRow = sheet.createRow(j + 2);
				if(null != order){
					currentRow.createCell(0).setCellValue(order.getSerialNum());
					currentRow.createCell(1).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(2).setCellValue(order.getName());
					String temp = null;
					if(SystemConfig.ORDERS_IMPORT_TYPE.equals(order.getCallStatus())) {
						temp = "正在输入";
					} else if(SystemConfig.ORDERS_UNDER_TYPE.equals(order.getCallStatus())) {
						temp = "已下单";
					}
					currentRow.createCell(3).setCellValue(temp);
					if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(order.getOrdersStatus())) {
						temp = "待审核";
					} else if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(order.getOrdersStatus())) {
						temp = "审核通过";
					} else if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(order.getOrdersStatus())) {
						temp = "审核未通过";
					} else if(SystemConfig.ORDERS_AUDIT_INVALID_TYPE.equals(order.getOrdersStatus())) {
						temp = "无效订单";
					} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(order.getOrdersStatus())) {
						temp = "取消订单";
					}
					currentRow.createCell(4).setCellValue(temp);
					if(SystemConfig.ORDERS_WAIT_DELIVERY_TYPE.equals(order.getWarehouseStatus())) {
						temp = "待发货";
					} else if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(order.getWarehouseStatus())) {
						temp = "已发货";
					} else if(SystemConfig.ORDERS_RETREAT_TYPE.equals(order.getWarehouseStatus())) {
						temp = "退单";
					} else if(SystemConfig.ORDERS_SIGN_TYPE.equals(order.getWarehouseStatus())) {
						temp = "签收";
					} else if(SystemConfig.ORDERS_NET_SIGN_TYPE.equals(order.getWarehouseStatus())) {
						temp = "网签";
					} else if(SystemConfig.ORDERS_NET_RETREAT_TYPE.equals(order.getWarehouseStatus())) {
						temp = "网退";
					}
					currentRow.createCell(5).setCellValue(temp);
					if(SystemConfig.BE_RECONCILED.equals(order.getFinancialStatus())){
						temp = "待对账";
					}else if(SystemConfig.HAS_RECONCILED.equals(order.getFinancialStatus())){
						temp = "已对账";
					}else if(SystemConfig.HAS_SECTIONED.equals(order.getFinancialStatus())){
						temp = "已回款";
					}
					currentRow.createCell(6).setCellValue(temp);
					currentRow.createCell(7).setCellValue(order.getContactNo());
					currentRow.createCell(8).setCellValue(order.getAmountPaid().toString());
					currentRow.createCell(9).setCellValue(order.getProvince().getpName());
					currentRow.createCell(10).setCellValue(order.getCity().getcName());
					currentRow.createCell(11).setCellValue(order.getDistrict().getdName());
					currentRow.createCell(12).setCellValue(order.getAddress());
					if(StringUtils.isNotBlank(order.getExpressId())){
						MedicalLogistics medicalLogistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
						currentRow.createCell(13).setCellValue(medicalLogistics.getName());
					}
					currentRow.createCell(14).setCellValue(order.getExpressNo());
					if(null != order.getDeliveryTime()){
						currentRow.createCell(15).setCellValue(DateFormatUtils.format(order.getDeliveryTime(),"yyyy-MM-dd HH:mm:ss"));
					}
					
					// 订单详细
					List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
					String ordersDetail = "";
					for(MedicalOrdersProduct ordersProduct : ordersProductList) {
						String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
						ordersDetail += product + ";";
					}
					if(StringUtils.isNotBlank(ordersDetail)){
						currentRow.createCell(16).setCellValue(ordersDetail);
					}
					
				}
			}
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportQueryOrdersAll(MedicalOrdersStorage ordersStorage) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(StringUtils.isNotBlank(ordersStorage.getSerialNum())) {
			statusSql.append("and a.serialNum=? ");
			objectList.add(ordersStorage.getSerialNum());
		}

		if(StringUtils.isNotBlank(ordersStorage.getExpressId())) {
			statusSql.append("and a.expressId=? ");
			objectList.add(ordersStorage.getExpressId());
		}

		if(StringUtils.isNotBlank(ordersStorage.getName())) {
			statusSql.append("and a.name=? ");
			objectList.add(ordersStorage.getName());
		}

		if(StringUtils.isNotBlank(ordersStorage.getZipCode())) {
			statusSql.append("and a.zipCode=? ");
			objectList.add(ordersStorage.getZipCode());
		}

		if(null != ordersStorage.getStartTime()){
			statusSql.append("and a.checkTime>=? ");
			objectList.add(ordersStorage.getStartTime());
		}

		if(null != ordersStorage.getEndTime()){
			statusSql.append("and a.checkTime<=? ");
			objectList.add(ordersStorage.getEndTime());
		}

		if(null != ordersStorage.getOrderTime()){
			statusSql.append("and a.createTime>=? ");
			objectList.add(ordersStorage.getOrderTime());
		}

		if(null != ordersStorage.getOrderTime2()){
			statusSql.append("and a.createTime<=? ");
			objectList.add(ordersStorage.getOrderTime2());
		}
		
		List<MedicalOrdersStorage> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.QUERY_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersStorage order = list.get(j);
			Row currentRow = sheet.createRow(j + 2);
			if(null != order){
				currentRow.createCell(0).setCellValue(order.getSerialNum());
				currentRow.createCell(1).setCellValue(DateFormatUtils.format(order.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(2).setCellValue(order.getName());
				String temp = null;
				if(SystemConfig.ORDERS_IMPORT_TYPE.equals(order.getCallStatus())) {
					temp = "正在输入";
				} else if(SystemConfig.ORDERS_UNDER_TYPE.equals(order.getCallStatus())) {
					temp = "已下单";
				}
				currentRow.createCell(3).setCellValue(temp);
				if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(order.getOrdersStatus())) {
					temp = "待审核";
				} else if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(order.getOrdersStatus())) {
					temp = "审核通过";
				} else if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(order.getOrdersStatus())) {
					temp = "审核未通过";
				} else if(SystemConfig.ORDERS_AUDIT_INVALID_TYPE.equals(order.getOrdersStatus())) {
					temp = "无效订单";
				} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(order.getOrdersStatus())) {
					temp = "取消订单";
				}
				currentRow.createCell(4).setCellValue(temp);
				if(SystemConfig.ORDERS_WAIT_DELIVERY_TYPE.equals(order.getWarehouseStatus())) {
					temp = "待发货";
				} else if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(order.getWarehouseStatus())) {
					temp = "已发货";
				} else if(SystemConfig.ORDERS_RETREAT_TYPE.equals(order.getWarehouseStatus())) {
					temp = "退单";
				} else if(SystemConfig.ORDERS_SIGN_TYPE.equals(order.getWarehouseStatus())) {
					temp = "签收";
				} else if(SystemConfig.ORDERS_NET_SIGN_TYPE.equals(order.getWarehouseStatus())) {
					temp = "网签";
				} else if(SystemConfig.ORDERS_NET_RETREAT_TYPE.equals(order.getWarehouseStatus())) {
					temp = "网退";
				}
				currentRow.createCell(5).setCellValue(temp);
				if(SystemConfig.BE_RECONCILED.equals(order.getFinancialStatus())){
					temp = "待对账";
				}else if(SystemConfig.HAS_RECONCILED.equals(order.getFinancialStatus())){
					temp = "已对账";
				}else if(SystemConfig.HAS_SECTIONED.equals(order.getFinancialStatus())){
					temp = "已回款";
				}
				currentRow.createCell(6).setCellValue(temp);
				currentRow.createCell(7).setCellValue(order.getContactNo());
				currentRow.createCell(8).setCellValue(order.getAmountPaid().toString());
				currentRow.createCell(9).setCellValue(order.getProvince().getpName());
				currentRow.createCell(10).setCellValue(order.getCity().getcName());
				currentRow.createCell(11).setCellValue(order.getDistrict().getdName());
				currentRow.createCell(12).setCellValue(order.getAddress());
				if(StringUtils.isNotBlank(order.getExpressId())){
					MedicalLogistics medicalLogistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
					currentRow.createCell(13).setCellValue(medicalLogistics.getName());
				}
				currentRow.createCell(14).setCellValue(order.getExpressNo());
				if(null != order.getDeliveryTime()){
					currentRow.createCell(15).setCellValue(DateFormatUtils.format(order.getDeliveryTime(),"yyyy-MM-dd HH:mm:ss"));
				}
				
				// 订单详细
				List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, order.getId());
				String ordersDetail = "";
				for(MedicalOrdersProduct ordersProduct : ordersProductList) {
					String product = ordersProduct.getMedicine().getName() + "*" + ordersProduct.getQuantity();
					ordersDetail += product + ";";
				}
				if(StringUtils.isNotBlank(ordersDetail)){
					currentRow.createCell(16).setCellValue(ordersDetail);
				}
				
			}
		}
		return workbook;
	}

	@SuppressWarnings({ "unchecked" })
	@Override
	public Workbook downloadDeliveryTemp(MedicalAdmin admin) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.MEDICINE_DELIVERY_MASTERPLATE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		//快递公司
		List<MedicalLogistics> melogisticsList = this.iBaseDao.getBySql("from MedicalLogistics a where a.hospitalId=? ", null, admin.getEnterpriseId());
		List<String> medicalLogisticsList = new ArrayList<String>();
		for(MedicalLogistics medicalLogistics : melogisticsList){
			medicalLogisticsList.add(medicalLogistics.getSerialNum() + "-" + medicalLogistics.getName());
		}
		String[] medicalLogistics = medicalLogisticsList.toArray(new String[melogisticsList.size()]);
		
		// 仓库
		List<MedicalWarehouse> mewarehouseList = this.iBaseDao.getBySql("from MedicalWarehouse m where m.hospitalId=? ", null,  admin.getEnterpriseId());
		List<String> medicalWarehouseList = new ArrayList<String>();
		for(MedicalWarehouse medicalWarehouse : mewarehouseList){
			medicalWarehouseList.add(medicalWarehouse.getCode() + "-" + medicalWarehouse.getName());
		}
		String[] medicalWarehouse = medicalWarehouseList.toArray(new String[mewarehouseList.size()]);
		
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列   
		sheet = ExcelUtil.setPullDown(sheet, medicalWarehouse, 1, 500, 3, 3);	// 第一列的前501行都设置为选择列表形式.
		sheet = ExcelUtil.setPullDown(sheet, medicalLogistics, 1, 500, 1, 1);
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalKeyValue> findSystemList(String typeName) {
		List<MedicalKeyValue> keyValList = this.iBaseDao.getBySql("from MedicalKeyValue a where a.typeName=? ", null, typeName);
		Iterator<MedicalKeyValue> keyValIter = keyValList.iterator();
		while(keyValIter.hasNext()) {
			MedicalKeyValue keyVal = keyValIter.next();
			if(SystemConfig.HM_SYSTEM_TYPE.equals(keyVal.getKeyName()) || 
					SystemConfig.SS_SYSTEM_TYPE.equals(keyVal.getKeyName()) || 
					SystemConfig.MM_SYSTEM_TYPE.equals(keyVal.getKeyName())) {
				keyValIter.remove();
			}
		}
		return keyValList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalKeyValue> findOrderStatusList(String systemType) {
		List<MedicalKeyValue> keyValList = this.iBaseDao.getBySql("from MedicalKeyValue a where a.typeName=? and a.systemType=? ", null, SystemConfig.ORDERS_STATUS, systemType);
		return keyValList;
	}
}